﻿Imports System.Data.OleDb
Imports DTO

Public Class KhachHangDAO

    'Public Shared Function ThemKhachHang(ByVal kh As KhachHangDTO, ByVal hd As HoaDonDTO, ByVal cthd As ChiTietHoaDonDTO) As Boolean

    '    Dim kq As Boolean = True

    '    Dim cn As OleDbConnection
    '    B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
    '    cn = DatabaseProvider.ConnectionData()
    '    B3: Tao chuoi strSQL thao tac CSDL
    '    Dim sql As String = "insert into KhachHang(TenKhachHang,SoDienThoai,DiaChi,Email,TongTienNo,DaXoa) values(?,?,?,?,?,?)"
    '    Dim cmd As New OleDbCommand(sql, cn)

    '    cmd.Parameters.Add("@TenKhachHang", OleDbType.WChar)
    '    cmd.Parameters.Add("@SoDienThoai", OleDbType.WChar)
    '    cmd.Parameters.Add("@DiaChi", OleDbType.WChar)
    '    cmd.Parameters.Add("@Email", OleDbType.WChar)
    '    cmd.Parameters.Add("@TongTienNo", OleDbType.Integer)
    '    cmd.Parameters.Add("@DaXoa", OleDbType.WChar)

    '    cmd.Parameters("@TenKhachHang").Value = kh.LayTenKhachHang
    '    cmd.Parameters("@SoDienThoai").Value = kh.LaySoDienThoai
    '    cmd.Parameters("@DiaChi").Value = kh.LayDiaChiKhachHang
    '    cmd.Parameters("@Email").Value = kh.layEmail
    '    cmd.Parameters("@TongTienNo").Value = kh.LayTongNo
    '    cmd.Parameters("@DaXoa").Value = 0

    '    Dim n As Integer = 0
    '    n = cmd.ExecuteNonQuery()

    '    sql = "Select @@IDENTITY"
    '    cmd = New OleDbCommand(sql, cn)
    '    kh.LayIDKhachHang = cmd.ExecuteScalar()
    '    If n <> 0 Then
    '        hd.layIDKhacHang = kh.LayIDKhachHang
    '        HoaDonDAO.ThemHoaDon(hd, cthd)
    '        kq = True

    '    Else
    '        kq = False
    '    End If


    '    cn.Close()

    '    Return kq

    'End Function

    Public Sub ThemKhachHang(ByVal kh As KhachHangDTO)

        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim sql As String = "insert into KhachHang(TenKhachHang,SoDienThoai,DiaChi,Email,TongTienNo,DaXoa) values(?,?,?,?,?,?)"
        Dim cmd As New OleDbCommand(sql, cn)

        cmd.Parameters.Add("@TenKhachHang", OleDbType.WChar)
        cmd.Parameters.Add("@SoDienThoai", OleDbType.WChar)
        cmd.Parameters.Add("@DiaChi", OleDbType.WChar)
        cmd.Parameters.Add("@Email", OleDbType.WChar)
        cmd.Parameters.Add("@TongTienNo", OleDbType.Integer)
        cmd.Parameters.Add("@DaXoa", OleDbType.Integer)

        cmd.Parameters("@TenKhachHang").Value = kh.LayTenKhachHang
        cmd.Parameters("@SoDienThoai").Value = kh.LaySoDienThoai
        cmd.Parameters("@DiaChi").Value = kh.LayDiaChiKhachHang
        cmd.Parameters("@Email").Value = kh.layEmail
        cmd.Parameters("@TongTienNo").Value = kh.LayTongNo
        cmd.Parameters("@DaXoa").Value = 0
        cmd.ExecuteNonQuery()
        cn.Close()
    End Sub

    Public Function LayKhachHang() As DataTable
        Dim kq As New DataTable

        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim sql As String = "select * from KhachHang where DaXoa=0 "
        Dim cmd As New OleDbCommand(sql, cn)

        Dim a As New OleDb.OleDbDataAdapter(sql, cn)
        a.SelectCommand = cmd
        a.Fill(kq)
        cn.Close()
        Return kq
    End Function

    Public Function LayKhachHangNo() As DataTable
        Dim kq As New DataTable

        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim sql As String = "select * from KhachHang where DaXoa=0  and TongTienNo > 0 "
        Dim cmd As New OleDbCommand(sql, cn)

        Dim a As New OleDb.OleDbDataAdapter(sql, cn)
        a.SelectCommand = cmd
        a.Fill(kq)
        cn.Close()
        Return kq
    End Function

    Public Function layTenKhachHang(ByVal kh As KhachHangDTO) As DataTable
        Dim kq As New DataTable

        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim sql As String = "select * from KhachHang where idKhachHang = ? "
        Dim cmd As New OleDbCommand(sql, cn)
        cmd.Parameters.Add("@idKhachHang", OleDbType.Integer)
        cmd.Parameters("@idKhachHang").Value = kh.LayIDKhachHang
        Dim a As New OleDb.OleDbDataAdapter(sql, cn)
        a.SelectCommand = cmd
        a.Fill(kq)
        cn.Close()
        Return kq

    End Function

    Public Function XoaKhachHang(ByVal kh As KhachHangDTO) As Boolean
        Dim kq As Boolean
        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim sql As String = "update KhachHang set DaXoa = 1 where idKhachHang = ? "
        Dim cmd As New OleDbCommand(sql, cn)
        cmd.Parameters.Add("@idKhachHang", OleDbType.Integer)
        cmd.Parameters("@idKhachHang").Value = kh.LayIDKhachHang

        cmd.ExecuteNonQuery()
        cn.Close()
        Return kq
    End Function

    Public Function updateKhachHang(ByVal kh As KhachHangDTO) As Boolean
        Dim kq As Boolean = True
        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim sql As String = "update KhachHang set TenKhachHang = ? , SoDienThoai = ? ,  DiaChi = ?, Email = ?, TongTienNo = ? where idKhachHang = ?"
        Dim cmd As New OleDbCommand(sql, cn)

        cmd.Parameters.Add("@TenKhachHang", OleDbType.WChar)
        cmd.Parameters.Add("@SoDienThoai", OleDbType.WChar)
        cmd.Parameters.Add("@DiaChi", OleDbType.WChar)
        cmd.Parameters.Add("@Email", OleDbType.WChar)
        cmd.Parameters.Add("@TongTienNo", OleDbType.Integer)
        cmd.Parameters.Add("@idKhachHang", OleDbType.Integer)

        cmd.Parameters("@TenKhachHang").Value = kh.LayTenKhachHang
        cmd.Parameters("@SoDienThoai").Value = kh.LaySoDienThoai
        cmd.Parameters("@DiaChi").Value = kh.LayDiaChiKhachHang
        cmd.Parameters("@Email").Value = kh.layEmail
        cmd.Parameters("@TongTienNo").Value = kh.LayTongNo
        cmd.Parameters("@idKhachHang").Value = kh.LayIDKhachHang
        cmd.ExecuteNonQuery()
        Dim i As Integer = 0
        i = cmd.ExecuteNonQuery()
        If i <> 0 Then
            kq = True
        Else
            kq = False
        End If
        cn.Close()
        Return kq
    End Function

    Public Sub updateNo(ByVal kh As KhachHangDTO, ByRef sl As Integer)

        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim sql As String = "update KhachHang set TongTienNo = ? where idKhachHang = ?"
        Dim cmd As New OleDbCommand(sql, cn)

        cmd.Parameters.Add("@TongTienNo", OleDbType.Integer)
        cmd.Parameters.Add("@idKhachHang", OleDbType.Integer)
        cmd.Parameters("@TongTienNo").Value = sl
        cmd.Parameters("@idKhachHang").Value = kh.LayIDKhachHang
        cmd.ExecuteNonQuery()

        cn.Close()

    End Sub

    Public Function LayTienNo(ByVal ph As PhieuThuDTO) As DataTable
        Dim kq As New DataTable
        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim sql As String = "select TongTienNo from KhachHang where idKhachHang = ? "
        Dim cmd As New OleDbCommand(sql, cn)
        cmd.Parameters.Add("@idKhachHang", OleDbType.Integer)
        cmd.Parameters("@idKhachHang").Value = ph.LayIDKhachHang
        Dim a As New OleDb.OleDbDataAdapter(sql, cn)
        a.SelectCommand = cmd
        a.Fill(kq)
        cn.Close()
        Return kq
    End Function

End Class
